#!/usr/bin/python3.4
#coding=utf-8
import io
import sys
import cgi, cgitb
import os,re,multiprocessing,time,IPy,pymysql
import xlrd
from multiprocessing import Pool
sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf-8')
# -*- coding: utf-8 -*-
def read_excel(filename):#读取调度IP，生成列表
    DDIP=xlrd.open_workbook(filename)
    #print(DDIP.sheet_names())
    DDIP_sh= DDIP.sheet_by_index(0)
    row_num=DDIP_sh.nrows
    #print(row_num)
    DDIP_list=[]
    for x in range(row_num):
        #print(DDIP_sh.row_values(x))
        DDIP_list.append(DDIP_sh.row_values(x))
    #print(DDIP_list)
    return  DDIP_list

def nsLookup(domainL):#domainL=[CP,Domain,mon]
    CP=domainL[0]
    domain=domainL[1]
    mon=domainL[2]
    cdn="无结果"
    answerName="无结果"
    ipA=[]
    p=re.compile(".+\..+")
    if p.match(domain):
        nsLk=os.popen('nslookup %s 113.215.2.222'  % domain).read()
        if nsLk.find('Name:')==-1:
            pass
        else:
            nsLk = nsLk[nsLk.find('Name:'):]
            #从nsLk中过滤出应答域名answer(nsLk)
            answerName=answer(nsLk)
            # 从nsLk中过滤出A记录 ipx(nsLk)
            cdn,ipA=ipx(nsLk)
    return [CP,domain,answerName,cdn,ipA,mon]

def answer(nsLk):#获取别名
    answerName=re.findall("Name:.+", nsLk)
    #print(answerName)
    answerName="".join(answerName)
    answerName=answerName.split("Name:")
    return answerName[1].strip()

def ipx(nsLk):#获取A记录
    ipA=re.findall(r"[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+", nsLk)
    #ipList = open('CDN.txt').read().split('\n')
    isCDN=CDNlist(ipA)#返回值[是否是cdn地址，所属cp]
    if isCDN[0]:
        cdn=isCDN[1]

    else:
        cdn="出网:"
    return cdn,ipA


def CDNlist(ipA):

    for ipC in ipA:#[A记录]
        Default = False
        cdn = "出网"
        for x in list(CDN)+list(DDHL):
            if ipC  in IPy.IP(x[0]):# 属于CDN网内
                cdn = "华通_" + x[1]
                Default = True
        if Default==False:
            return [Default,cdn]
    return [Default,cdn]



def readDB(DBname):
    db = pymysql.connect("113.215.1.161", "root", "root", "mydb", charset="utf8")  # 打开数据库连接
    cursor = db.cursor()  # 使用cursor()方法获取操作游标
    sql = "SELECT * FROM %s" % DBname
    cursor.execute(sql)
    results = cursor.fetchall()
    db.close()
    return results

def updateDB(IP,flag):#[CP,domain,answerName,cdn,ipA,mon]
    t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    db=pymysql.connect("113.215.1.161","root","root","mydb",charset="utf8" )# 打开数据库连接
    cursor = db.cursor()# 使用cursor()方法获取操作游标
    #查询
    sqlFind='SELECT * FROM CDN_domainTab WHERE Domain="%s"' % IP[1]
    #更新
    sqlupdate='UPDATE CDN_domainTab SET mon="%s",Cname="%s",CDN="%s",IPlist="%s",lastTime="%s",flag="%s" WHERE Domain="%s" ' % (IP[5],IP[2],IP[3],IP[4],t,flag,IP[1])
    #插入
    sqlInsert='INSERT INTO CDN_domainTab(CP,Domain,Cname,CDN,IPlist,mon) VALUES ("%s","%s","%s","%s","%s","%s")' % tuple(IP)
    print(sqlFind)
    Find=cursor.execute(sqlFind)
    print("找到%s条记录" % Find)
    if Find==0:#如果库中无次记录
        try:
            cursor.execute(sqlInsert)  # 插入记录
            print(sqlInsert,"succese")
        except:
            print(sqlInsert,"failed")
            db.close()
            return 0
    else:
        try:
            cursor.execute(sqlupdate)#更新记录
            print(sqlupdate,"succese")
        except:
            print(sqlupdate, "failed")
            db.close()
            return 0
    db.close()

def mail():
    pass
    db = pymysql.connect("113.215.1.161", "root", "root", "mydb", charset="utf8")  # 打开数据库连接
    cursor = db.cursor()  # 使用cursor()方法获取操作游标
    sql = "SELECT * FROM CDN_domainTab WHERE mon='yes' AND flag=1"
    count=cursor.execute(sql)
    print("mailcount",count)
    results = cursor.fetchall()


    if count!=0:
        with open('/tmp/CDNmail.txt', 'w') as fmail:
            for x in results:
                mailline="%s\t域名:%s\t调度别名:%s\t业务应用:%s\t解析结果:%s\n" % (x[4],x[1],x[3],x[0],x[5])
                mailline=mailline.replace("<br>","\t")
                fmail.write(mailline)
        os.system('/bin/mail -s "CDN资源检测到出网" ganlian@watone.com.cn  < /tmp/CDNmail.txt')
        os.system('/bin/mail -s "CDN资源检测到出网" service@watone.com.cn  < /tmp/CDNmail.txt')

    db.close()



if __name__ == '__main__':
   #CDN = read_excel('/var/www/html/DDIP/CDN.xls')
   CDN=list(readDB("CDN_Addr"))
   #DDHL = read_excel('/var/www/html/DDIP/DDHL.xls')
   DDHL =list(readDB("DDHLtab"))#+list(readDB("DDHLtab_other"))
   DDHL_1=[]
   for x in DDHL:
        DDHL_1.append([x[0],"对等互联_"+x[1]])
   DDHL=DDHL_1
   domainType = read_excel('/var/www/html/DDIP/DomainType.xls')
   domainType = read_excel('/var/www/html/DDIP/DomainType.xls')
    # 清空告警邮件文本
   fmail = open('/tmp/CDNmail.txt', 'w')
   fmail.write("http://113.215.1.161\n")
   fmail.close()
   # 是否发告警邮件,默认为否
   mailDefault = False
   #domain=open("domain_list.txt").read().split('\n')
   domain = readDB("CDN_domainTab")#[CP,Domain,mon,Cname,CDN,IPlist,lastTime,flag]
   #print(domain)
   server = '113.215.2.222'
   # 开启50个进程，进行解析
   p = Pool(50)
   domainList=[]
   for x in domain:
        x=list(x)
        x=x[0:3]
        domainList.append(x)#domainLIst=[[CP,Domain,mon],[CP,Domain,mon]...]

   print(domainList)
   res=p.map(nsLookup,domainList)#[[CP,domain,answerName,cdn,ipA,mon],[CP,domain,answerName,cdn,ipA,mon]...]
   p.close()
   p.join()
   t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
   with open('/var/www/html/CDNlog/%sLog.txt' % time.strftime("%Y-%m-%d", time.localtime()), 'a') as flog:
       for y in res:#y=[CP,domain,answerName,cdn,ipA,mon]
           y[4] = "<br>".join(y[4])
           if "华通" not in y[3]:
               flag=1
               for z in domainType:
                   if "." + z[0] in y[2]:
                       y[3] = z[1] + "出网"
           else:
               flag=0
               y[5]="yes"
           logline=t+"\t%s\t%s\t%s\t%s\t%s\t%s\n" % tuple(y)
           logline=logline.replace("<br>","\t")
           flog.write(logline)#[CP,domain,answerName,cdn,ipA,mon]
           updateDB(y,flag)#[CP,domain,answerName,cdn,ipA,mon]
   mail()




